/*
 * frmSimpleTableEdit.java
 *
 * Created on 1. März 2008, 10:53
 */

package com.googlecode.oskis.admin;
import java.sql.*;
import com.googlecode.oskis.library.db.DBQuery;
import com.googlecode.oskis.library.date.DateHelper;
import com.googlecode.oskis.library.misc.ColorConstants;
import com.googlecode.oskis.library.db.SQLErrorDialog;
import javax.swing.JOptionPane;

/**
 * SimpleTableEdit ist ein Dialog zum Bearbeiten von einfachen Stammtabellen,
 * die nur aus den Spalten Schlüssel,bezeichnung,gültig von und gültig bis bestehen,
 * z.B. Titel, Geschlecht etc
 * @author  thomas
 */
public class frmSimpleTableEdit extends javax.swing.JFrame {
    
    private Connection db;
    private DBQuery q;
    private String key,tablename;
    
    /** Übergabeparameter des Kontruktors:
        Datenbankverbindung, Name der Stammtabellen, Name des Schlüsselfeldes und Text des Schlüsselfeödes im Formular
     */
    public frmSimpleTableEdit(Connection db,String tablename,String key,String keytext) {
        initComponents();
        this.db=db;
        q=new DBQuery(db);
        this.setTitle("Tabelle "+tablename+" bearbeiten");
        lblKeytext.setText(keytext);
        this.key=key;
        this.tablename=tablename;
        refreshTable();
    }
    
    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        lblKeytext = new javax.swing.JLabel();
        txtKey = new javax.swing.JTextField();
        lblBezeichnung = new javax.swing.JLabel();
        txtBezeichnung = new javax.swing.JTextField();
        jScrollPane1 = new javax.swing.JScrollPane();
        tblQuery = new javax.swing.JTable();
        btnClose = new javax.swing.JButton();
        btnNew = new javax.swing.JButton();
        btnEdit = new javax.swing.JButton();
        btnDelete = new javax.swing.JButton();
        lblDatf = new javax.swing.JLabel();
        lblDatt = new javax.swing.JLabel();
        txtDatf = new javax.swing.JTextField();
        txtDatt = new javax.swing.JTextField();

        setDefaultCloseOperation(javax.swing.WindowConstants.DISPOSE_ON_CLOSE);

        lblKeytext.setText("keyText");

        txtKey.setNextFocusableComponent(txtBezeichnung);
        txtKey.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusGained(java.awt.event.FocusEvent evt) {
                txtKeyFocusGained(evt);
            }
            public void focusLost(java.awt.event.FocusEvent evt) {
                txtKeyFocusLost(evt);
            }
        });

        lblBezeichnung.setText("Bezeichnung");

        txtBezeichnung.setNextFocusableComponent(txtDatf);
        txtBezeichnung.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusGained(java.awt.event.FocusEvent evt) {
                txtBezeichnungFocusGained(evt);
            }
            public void focusLost(java.awt.event.FocusEvent evt) {
                txtBezeichnungFocusLost(evt);
            }
        });

        tblQuery.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null}
            },
            new String [] {
                "Title 1", "Title 2", "Title 3", "Title 4"
            }
        ));
        tblQuery.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                tblQueryMouseClicked(evt);
            }
        });
        jScrollPane1.setViewportView(tblQuery);

        btnClose.setText("Schließen");
        btnClose.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnCloseActionPerformed(evt);
            }
        });

        btnNew.setMnemonic('N');
        btnNew.setText("Neu");
        btnNew.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnNewActionPerformed(evt);
            }
        });

        btnEdit.setMnemonic('e');
        btnEdit.setText("Ändern");
        btnEdit.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnEditActionPerformed(evt);
            }
        });

        btnDelete.setMnemonic('L');
        btnDelete.setText("Löschen");
        btnDelete.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnDeleteActionPerformed(evt);
            }
        });

        lblDatf.setText("Gültig von");

        lblDatt.setText("Gültig bis");

        txtDatf.setNextFocusableComponent(txtDatt);
        txtDatf.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusGained(java.awt.event.FocusEvent evt) {
                txtDatfFocusGained(evt);
            }
            public void focusLost(java.awt.event.FocusEvent evt) {
                txtDatfFocusLost(evt);
            }
        });

        txtDatt.setNextFocusableComponent(txtKey);
        txtDatt.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusGained(java.awt.event.FocusEvent evt) {
                txtDattFocusGained(evt);
            }
            public void focusLost(java.awt.event.FocusEvent evt) {
                txtDattFocusLost(evt);
            }
        });

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addGroup(javax.swing.GroupLayout.Alignment.LEADING, layout.createSequentialGroup()
                        .addGap(13, 13, 13)
                        .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 593, Short.MAX_VALUE))
                    .addGroup(layout.createSequentialGroup()
                        .addContainerGap()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                            .addGroup(layout.createSequentialGroup()
                                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                    .addComponent(lblBezeichnung)
                                    .addComponent(lblKeytext))
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                    .addComponent(txtKey, javax.swing.GroupLayout.DEFAULT_SIZE, 514, Short.MAX_VALUE)
                                    .addComponent(txtBezeichnung, javax.swing.GroupLayout.DEFAULT_SIZE, 514, Short.MAX_VALUE)
                                    .addComponent(txtDatf, javax.swing.GroupLayout.DEFAULT_SIZE, 514, Short.MAX_VALUE)
                                    .addComponent(txtDatt, javax.swing.GroupLayout.DEFAULT_SIZE, 514, Short.MAX_VALUE)))
                            .addComponent(btnClose, javax.swing.GroupLayout.Alignment.LEADING)))
                    .addGroup(javax.swing.GroupLayout.Alignment.LEADING, layout.createSequentialGroup()
                        .addContainerGap()
                        .addComponent(lblDatf))
                    .addGroup(javax.swing.GroupLayout.Alignment.LEADING, layout.createSequentialGroup()
                        .addContainerGap()
                        .addComponent(lblDatt))
                    .addGroup(javax.swing.GroupLayout.Alignment.LEADING, layout.createSequentialGroup()
                        .addContainerGap()
                        .addComponent(btnNew)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(btnEdit)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(btnDelete)))
                .addContainerGap())
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblKeytext)
                    .addComponent(txtKey, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblBezeichnung)
                    .addComponent(txtBezeichnung, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblDatf)
                    .addComponent(txtDatf, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(lblDatt)
                    .addComponent(txtDatt, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(btnNew)
                    .addComponent(btnEdit)
                    .addComponent(btnDelete))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 266, Short.MAX_VALUE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(btnClose)
                .addContainerGap())
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void btnCloseActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnCloseActionPerformed
        this.setVisible(false);
    }//GEN-LAST:event_btnCloseActionPerformed

    private void btnNewActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnNewActionPerformed
        String keytext=txtKey.getText();
        String bezeichnung=txtBezeichnung.getText();
        String datf=txtDatf.getText();
        String datt=txtDatt.getText();
        String SQL="insert into "+tablename+"("+key+",bezeichnung,datf,datt)" +
                "   values ('"+keytext+"','"+bezeichnung+"'," +
                "   to_timestamp('"+datf+"','DD.MM.YYYY HH24:MI:SS')," +
                "   to_timestamp('"+datt+"','DD.MM.YYYY HH24:MI:SS'))";
        try {
            Statement st=db.createStatement();
            st.execute(SQL);
            st.close();
            refreshTable();
        } catch(SQLException e) {
            new SQLErrorDialog(this,"PG",e,SQL).setVisible(true);
        }
    }//GEN-LAST:event_btnNewActionPerformed

    private void txtKeyFocusGained(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtKeyFocusGained
        txtKey.selectAll();
        txtKey.setBackground(ColorConstants.COLOR_FOCUS);
    }//GEN-LAST:event_txtKeyFocusGained

    private void txtBezeichnungFocusGained(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtBezeichnungFocusGained
        txtBezeichnung.selectAll();
        txtBezeichnung.setBackground(ColorConstants.COLOR_FOCUS);
    }//GEN-LAST:event_txtBezeichnungFocusGained

    private void txtDatfFocusGained(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtDatfFocusGained
        txtDatf.selectAll();
        txtDatf.setBackground(ColorConstants.COLOR_FOCUS);
    }//GEN-LAST:event_txtDatfFocusGained

    private void txtDattFocusGained(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtDattFocusGained
        txtDatt.selectAll();
        txtDatt.setBackground(ColorConstants.COLOR_FOCUS);
    }//GEN-LAST:event_txtDattFocusGained

    private void txtDatfFocusLost(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtDatfFocusLost
        DateHelper dh=new DateHelper(txtDatf.getText());
        dh.setFormatString("dd.MM.yyyy HH:mm:ss");
        if (dh.getFormatSuccess()) {
            txtDatf.setText(dh.getFormattedDate());
        }
        txtDatf.setBackground(ColorConstants.COLOR_NO_FOCUS);
    }//GEN-LAST:event_txtDatfFocusLost

    private void txtDattFocusLost(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtDattFocusLost
        DateHelper dh=new DateHelper(txtDatt.getText());
        dh.setFormatString("dd.MM.yyyy HH:mm:ss");
        if (dh.getFormatSuccess()) {
            txtDatt.setText(dh.getFormattedDate());
        }
        txtDatt.setBackground(ColorConstants.COLOR_NO_FOCUS);
    }//GEN-LAST:event_txtDattFocusLost

    private void txtKeyFocusLost(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtKeyFocusLost
        txtKey.setBackground(ColorConstants.COLOR_NO_FOCUS);
    }//GEN-LAST:event_txtKeyFocusLost

    private void txtBezeichnungFocusLost(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_txtBezeichnungFocusLost
        txtBezeichnung.setBackground(ColorConstants.COLOR_NO_FOCUS);
    }//GEN-LAST:event_txtBezeichnungFocusLost

    private void btnEditActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnEditActionPerformed
        String keytext=txtKey.getText();
        String bezeichnung=txtBezeichnung.getText();
        String datf=txtDatf.getText();
        String datt=txtDatt.getText();
        String SQL="update "+tablename+
                "   set bezeichnung='"+bezeichnung+"',"+
                "   datf=to_timestamp('"+datf+"','DD.MM.YYYY HH24:MI:SS')," +
                "   datt=to_timestamp('"+datt+"','DD.MM.YYYY HH24:MI:SS')" +
                "   where "+key+" = '"+keytext+"'";
        try {
            Statement st=db.createStatement();
            st.execute(SQL);
            st.close();
            refreshTable();
        } catch(SQLException e) {
            new SQLErrorDialog(this,"PG",e,SQL).setVisible(true);
        }
    }//GEN-LAST:event_btnEditActionPerformed

    private void btnDeleteActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnDeleteActionPerformed
        String keytext=txtKey.getText();
        String SQL="delete from "+tablename+
                "   where "+key+"='"+keytext+"'";
        try {
            Statement st=db.createStatement();
            st.execute(SQL);
            st.close();
            refreshTable();
        } catch(SQLException e) {
            new SQLErrorDialog(this,"PG",e,SQL).setVisible(true);
        }
    }//GEN-LAST:event_btnDeleteActionPerformed

    private void tblQueryMouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_tblQueryMouseClicked
        int row=tblQuery.getSelectedRow();
        String keytext=(String)tblQuery.getValueAt(row,0);
        String bezeichnung=(String)tblQuery.getValueAt(row,1);
        String datf=(String)tblQuery.getValueAt(row,2);
        String datt=(String)tblQuery.getValueAt(row,3);
        txtKey.setText(keytext);
        txtBezeichnung.setText(bezeichnung);
        txtDatf.setText(datf);
        txtDatt.setText(datt);
    }//GEN-LAST:event_tblQueryMouseClicked
    

    private void refreshTable() {
        if (q.executeQuery("select "+key+",bezeichnung,to_char(datf,'DD.MM.YYYY HH24:MI:SS') as datf," +
                "           to_char(datt,'DD.MM.YYYY HH24:MI:SS') as datt from "+tablename)) {
            tblQuery.setModel(q.getTableModel());
        } else {
           JOptionPane.showMessageDialog(this,q.getSQLError());
        }
    }
    
    
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton btnClose;
    private javax.swing.JButton btnDelete;
    private javax.swing.JButton btnEdit;
    private javax.swing.JButton btnNew;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JLabel lblBezeichnung;
    private javax.swing.JLabel lblDatf;
    private javax.swing.JLabel lblDatt;
    private javax.swing.JLabel lblKeytext;
    private javax.swing.JTable tblQuery;
    private javax.swing.JTextField txtBezeichnung;
    private javax.swing.JTextField txtDatf;
    private javax.swing.JTextField txtDatt;
    private javax.swing.JTextField txtKey;
    // End of variables declaration//GEN-END:variables
    
}
